package com.xpgk.mvc.statistic.zbStatistic;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.xpgk.mvc.base.BaseController;
import com.xpgk.mvc.organization.Organization;
import com.xpgk.mvc.statistic.dailyTotalStatistic.DailyNursingTotal;
import com.xpgk.mvc.statistic.monthTotalStatistic.MonthTotal;
import com.xpgk.mvc.user.User;

public class ZbMonthTotalStatisticController extends  BaseController{
	
	public  void  index()
	{	
		Organization organization = Organization.dao.findById(getCUserOrganIds());
		setAttr("organization", organization);
		render("zbMonthTotal.html");
	}
	
	public  void  getZbMonthTotal()
	{
		String monthStr = getPara("date");//2018-04
		String year = monthStr.substring(0, 4);//2018
		User user = getCUser();//当前用户是否是总部账号
		String orgSql = "SELECT b.province as organids,b.provinceName as orgname FROM sb_customer_total_daily a LEFT JOIN sys_organization b ON a.organids = b.ids WHERE  DATE_FORMAT(date,  '%Y-%m') = ?  GROUP BY b.province";
		List<Record> orgList =null;
		
		String sql  = getSqlMy(MonthTotal.sqlId_zbgetOnemonthTotalList);
		String dntsql  = getSqlMy(MonthTotal.sqlId_zbgetmonthTotalList);
		String dnurse = "SELECT COUNT( a.ids ) as fwrs,SUM(gjWeight) as sjjzjs FROM sb_customer_service_nurse a  LEFT JOIN sys_organization b ON a.organids = b.ids WHERE YearMon = ? and b.province = ?";
		String yjjsSql = "SELECT SUM(delWeight) as yjjs from sb_customer_detail a LEFT JOIN sys_organization b on a.organIds = b.ids WHERE  DATE_FORMAT(firstDate, '%Y-%m') = ? AND province = ? and a.dataStatus in ('1','2')";
		//String xgkSql = "SELECT customerIds FROM sb_customer_detail a LEFT JOIN sys_organization b on a.organIds = b.ids WHERE  DATE_FORMAT(firstDate, '%Y-%m') = ? AND province = ? and a.dataStatus in ('1','2')";
		String sgksjjsSql = "SELECT sum( gjWeight ) as gjWeight from sb_customer_service_nurse a LEFT JOIN sys_organization b ON a.organids = b.ids  WHERE province  = ? and YearMon = ? and isXgk = '1'";
		String sjjsSql = "SELECT SUM(gjWeight) as sjjs from sb_customer_service_nurse a  LEFT JOIN sys_organization b ON a.organids = b.ids WHERE b.province  = ? and YearMon = ?";
		List<Record> records = new ArrayList<>();
		if(user.getStr(User.column_stationids)!=null && !user.getStr(User.column_stationids).equals("7197dab3f7b04363b40d83886366bce2")){
			orgSql = "SELECT b.parentIds as organids,c.name as orgname FROM sb_customer_total_daily a LEFT JOIN sys_organization b ON a.organids = b.ids LEFT JOIN sys_organization c ON b.parentIds = c.ids WHERE  DATE_FORMAT(date,  '%Y-%m') = ?  and c.mangerIds = ? GROUP BY b.parentIds";
			orgList =Db.find(orgSql,monthStr,getCUserIds());
			sql  = getSqlMy(MonthTotal.sqlId_dqjlgetOnemonthTotalList);
			dntsql  = getSqlMy(MonthTotal.sqlId_dqjlgetmonthTotalList);
			dnurse = "SELECT COUNT( a.ids ) as fwrs,SUM(gjWeight) as sjjzjs FROM sb_customer_service_nurse a  LEFT JOIN sys_organization b ON a.organids = b.ids WHERE YearMon = ? and b.parentIds = ?";
			yjjsSql = "SELECT SUM(delWeight) as yjjs from sb_customer_detail a LEFT JOIN sys_organization b on a.organIds = b.ids WHERE  DATE_FORMAT(firstDate, '%Y-%m') = ? AND parentIds = ? and a.dataStatus in ('1','2')";
			//String xgkSql = "SELECT customerIds FROM sb_customer_detail a LEFT JOIN sys_organization b on a.organIds = b.ids WHERE  DATE_FORMAT(firstDate, '%Y-%m') = ? AND province = ? and a.dataStatus in ('1','2')";
			sgksjjsSql = "SELECT sum( gjWeight ) as gjWeight from sb_customer_service_nurse a LEFT JOIN sys_organization b ON a.organids = b.ids  WHERE parentIds  = ? and YearMon = ? and isXgk = '1'";
			sjjsSql = "SELECT SUM(gjWeight) as sjjs from sb_customer_service_nurse a  LEFT JOIN sys_organization b ON a.organids = b.ids WHERE b.parentIds  = ? and YearMon = ?";
			
		}else{
			orgList =Db.find(orgSql,monthStr);
		}
		
		if(orgList!=null && orgList.size()>0){
			for(int i = 0;i<orgList.size();i++){
				double yjjsSum = 0d;
				double sjjsSum = 0d;
				String organIds = orgList.get(i).getStr("organids");
				List<Record>  dntList =  Db.find(dntsql, organIds,year);
				Record  r =  Db.findFirst(sql,organIds ,monthStr);//某机构 某月的查询表数据
				//添加服务人数、实际减重斤数
				Record record = Db.findFirst(dnurse,monthStr,organIds);
				if(record!=null){
					String fwrs = record.getLong("fwrs")+"";
					if(fwrs!=null && !fwrs.equals("")){
						r.set("fwrs", fwrs);
					}
					if(record.getDouble("sjjzjs")!=null ){
						r.set("sjjzjs", new DecimalFormat("#.0").format( record.getDouble("sjjzjs")) );
					}
				}
				
				//添加当年当月新增顾客应减斤数
				Record yjjsRecord = Db.findFirst(yjjsSql,monthStr,organIds);
				double yjjs = 0d;
				if(yjjsRecord.getDouble("yjjs")!=null ){
					r.set("xgkyjjs", yjjsRecord.getDouble("yjjs"));
					yjjs =yjjsRecord.getDouble("yjjs");
				}
				//添加当年当月新增顾客实减斤数
				Record xgkRecord = Db.findFirst(sgksjjsSql,organIds,monthStr);
				double sjjs = 0d;
				if(xgkRecord.getDouble("gjWeight")!=null ){
					r.set("dndyxgksjjs", xgkRecord.getDouble("gjWeight"));
				}
				/*if(xgkRecord!=null && xgkRecord.size()>0){
					for (Record xgk:xgkRecord) {
						String customerIds = xgk.getStr("customerIds");
						Record gjRecord = Db.findFirst(sgksjjsSql,customerIds,monthStr);
						if(gjRecord!=null ){
							if(gjRecord.getFloat("gjWeight")!=null ){
								sjjs = sjjs+gjRecord.getFloat("gjWeight");
								
							}
							
						}
					}
					//sjjsSum = sjjsSum + sjjs;
					r.set("dndyxgksjjs", new DecimalFormat("0.0").format( sjjs) );
				}*/
				//添加实减斤数
				Record sjjsRecord = Db.findFirst(sjjsSql,organIds,monthStr);
				if(sjjsRecord!=null && sjjsRecord.getDouble("sjjs")!=null){
					r.set("sjjzjs", sjjsRecord.getDouble("sjjs"));
				}
				//添加累计减重率
				
				for(Record dnt:dntList){
					
					int month = dnt.getInt("date");
					String monthStr2 = month+"";
					if(month<10){
						monthStr2 = "0"+monthStr2;
					}
					String yearMonth = year+"-"+monthStr2;//2017-04
					
					//当年当月新增顾客应减斤数
					Record yjjsRecord1 = Db.findFirst(yjjsSql,yearMonth,organIds);
					double yjjs1 = 0d;
					if(yjjsRecord1.getDouble("yjjs")!=null ){
						yjjs1 =yjjsRecord1.getDouble("yjjs");
						yjjsSum = yjjsSum +yjjs1;
					}
					if(yjjsSum!=0){
					//当年当月新增顾客实减斤数
					//List<Record> xgkRecord1 = Db.find(xgkSql,yearMonth,organIds);
					double sjjs1 = 0d;
					Record xgkRecord1 = Db.findFirst(sgksjjsSql,organIds,yearMonth);
					
					if(xgkRecord1.getDouble("gjWeight")!=null ){
						sjjsSum = xgkRecord1.getDouble("gjWeight");
					}
					
					
					/*if(xgkRecord1!=null && xgkRecord1.size()>0){
						for (Record xgk:xgkRecord1) {
							String customerIds = xgk.getStr("customerIds");
							Record gjRecord = Db.findFirst(sgksjjsSql,customerIds,yearMonth);
							if(gjRecord!=null ){
								if(gjRecord.getFloat("gjWeight")!=null ){
									sjjs1 = sjjs1+gjRecord.getFloat("gjWeight");
								}
								
							}
						}
						sjjsSum = sjjsSum + sjjs1;
					}*/
					if(monthStr.equals(yearMonth)){
						r.set("ljjzl", (sjjsSum/yjjsSum)*100);
						break;
					}
				}
			}
			
				r.set("orgname", orgList.get(i).getStr("orgname"));
				r.set("organids", orgList.get(i).getStr("organids"));
				records.add(r);
			}
		}
		renderJson(records);
		/*String date  = getPara("date");
		String organids = getPara("organIds");
		Organization organization = Organization.dao.findById(organids);
		if(organization.getInt(Organization.column_level) == 4)//门店级别
		{	
			String sql = "SELECT sctm.*,so.name orgname,so.kyTime,TIMESTAMPDIFF(MONTH,so.kyTime,NOW()) dl "+
						 " FROM sb_customer_total_month  sctm  LEFT JOIN  sys_organization  so  ON sctm.organids = so.ids "+
						 " WHERE sctm.organids = ? AND  sctm.date= ? ";
			List<MonthTotal>  dnt = MonthTotal.dao.find(sql,organids,date);
			renderJson(dnt);
		}else if(organization.getInt(Organization.column_level) == 3)//代理商级别
		{
			splitPage.getQueryParam().put("date", date);
			splitPage.getQueryParam().put("organids", organids);
			String json = paging(splitPage,MonthTotal.sqlId_getMonthTotalSelect,MonthTotal.sqlId_getMonthTotalForm);
			renderJson(json);
		}else
		{
			renderJson("");
		}*/
	}
	
	public  void  tolookInfo()
	{
		
		String organids = getPara("organids");
		setAttr("organids", organids);
		render("zbMonthTotalInfo.html");
	
	}
	public  void  lookInfo()
	{
		
		String organids = getPara("_query.organIds");
		
		String monthStr = getPara("date");//2018-04
		String year = monthStr.substring(0, 4);//2018
		String orgSql = "SELECT b.parentIds as organids,c.name as orgname FROM sb_customer_total_daily a LEFT JOIN sys_organization b ON a.organids = b.ids LEFT JOIN sys_organization c ON b.parentIds = c.ids  WHERE  DATE_FORMAT(date,  '%Y-%m') = ? AND b.province =?  GROUP BY b.parentIds";
		List<Record> orgList =null;
		orgList =Db.find(orgSql,monthStr,organids);
		String sql  = getSqlMy(MonthTotal.sqlId_zbgetOnemonthTotalListInfo);
		String dntsql  = getSqlMy(MonthTotal.sqlId_zbgetmonthTotalListInfo);
		String dnurse = "SELECT COUNT( a.ids ) as fwrs,SUM(gjWeight) as sjjzjs FROM sb_customer_service_nurse a  LEFT JOIN sys_organization b ON a.organids = b.ids WHERE YearMon = ? and b.parentIds = ?";
		String yjjsSql = "SELECT SUM(delWeight) as yjjs from sb_customer_detail a LEFT JOIN sys_organization b on a.organIds = b.ids WHERE  DATE_FORMAT(firstDate, '%Y-%m') = ? AND b.parentIds = ? and a.dataStatus in ('1','2')";
		//String xgkSql = "SELECT customerIds FROM sb_customer_detail a LEFT JOIN sys_organization b on a.organIds = b.ids WHERE  DATE_FORMAT(firstDate, '%Y-%m') = ? AND province = ? and a.dataStatus in ('1','2')";
		String sgksjjsSql = "SELECT sum( gjWeight ) as gjWeight from sb_customer_service_nurse a LEFT JOIN sys_organization b ON a.organids = b.ids  WHERE b.parentIds  = ? and YearMon = ? and isXgk = '1'";
		String sjjsSql = "SELECT SUM(gjWeight) as sjjs from sb_customer_service_nurse a  LEFT JOIN sys_organization b ON a.organids = b.ids WHERE b.parentIds  = ? and YearMon = ?";
		
		List<Record> records = new ArrayList<>();
		Organization org = Organization.dao.findById(organids);
		if(org!=null ){
			if(org.getInt(Organization.column_level) == 3){//市
				orgSql = "SELECT a.organids as organids ,b.name as orgname FROM sb_customer_total_daily a LEFT JOIN sys_organization b ON a.organids = b.ids WHERE b.parentIds = ? AND DATE_FORMAT(date,  '%Y-%m') = ?  GROUP BY a.organids";
				orgList =Db.find(orgSql,organids,monthStr);
				sql  = getSqlMy(MonthTotal.sqlId_getOnemonthTotalList);
				dntsql  = getSqlMy(MonthTotal.sqlId_getmonthTotalList);
				dnurse = "SELECT COUNT(ids) as fwrs,SUM(gjWeight) as sjjzjs FROM sb_customer_service_nurse WHERE YearMon = ? and organids = ?";
				yjjsSql = "SELECT SUM(delWeight) as yjjs from sb_customer_detail WHERE  DATE_FORMAT(firstDate, '%Y-%m') = ? AND organIds = ? and dataStatus in ('1','2')";
				sgksjjsSql = "SELECT sum( gjWeight ) as gjWeight from sb_customer_service_nurse WHERE organids = ? and YearMon = ? and isXgk = '1'";
				sjjsSql = "SELECT SUM(gjWeight) as sjjs from sb_customer_service_nurse WHERE organids = ? and YearMon = ?";
				
			}
		}
		if(orgList!=null && orgList.size()>0){
			for(int i = 0;i<orgList.size();i++){
				double yjjsSum = 0d;
				double sjjsSum = 0d;
				String organIds = orgList.get(i).getStr("organids");
				List<Record>  dntList =  Db.find(dntsql, organIds,year);
				Record  r =  Db.findFirst(sql,organIds ,monthStr);//某机构 某月的查询表数据
				//添加服务人数、实际减重斤数
				Record record = Db.findFirst(dnurse,monthStr,organIds);
				if(record!=null){
					String fwrs = record.getLong("fwrs")+"";
					if(fwrs!=null && !fwrs.equals("")){
						r.set("fwrs", fwrs);
					}
					if(record.getDouble("sjjzjs")!=null ){
						r.set("sjjzjs", new DecimalFormat("#.0").format( record.getDouble("sjjzjs")) );
					}
				}
				
				//添加当年当月新增顾客应减斤数
				Record yjjsRecord = Db.findFirst(yjjsSql,monthStr,organIds);
				double yjjs = 0d;
				if(yjjsRecord.getDouble("yjjs")!=null ){
					r.set("xgkyjjs", yjjsRecord.getDouble("yjjs"));
					yjjs =yjjsRecord.getDouble("yjjs");
				}
				//添加当年当月新增顾客实减斤数
				Record xgkRecord = Db.findFirst(sgksjjsSql,organIds,monthStr);
				double sjjs = 0d;
				if(xgkRecord.getDouble("gjWeight")!=null ){
					r.set("dndyxgksjjs", xgkRecord.getDouble("gjWeight"));
				}
				/*if(xgkRecord!=null && xgkRecord.size()>0){
					for (Record xgk:xgkRecord) {
						String customerIds = xgk.getStr("customerIds");
						Record gjRecord = Db.findFirst(sgksjjsSql,customerIds,monthStr);
						if(gjRecord!=null ){
							if(gjRecord.getFloat("gjWeight")!=null ){
								sjjs = sjjs+gjRecord.getFloat("gjWeight");
								
							}
							
						}
					}
					//sjjsSum = sjjsSum + sjjs;
					r.set("dndyxgksjjs", new DecimalFormat("0.0").format( sjjs) );
				}*/
				//添加实减斤数
				Record sjjsRecord = Db.findFirst(sjjsSql,organIds,monthStr);
				if(sjjsRecord!=null && sjjsRecord.getDouble("sjjs")!=null){
					r.set("sjjzjs", sjjsRecord.getDouble("sjjs"));
				}
				//添加累计减重率
				
				for(Record dnt:dntList){
					
					int month = dnt.getInt("date");
					String monthStr2 = month+"";
					if(month<10){
						monthStr2 = "0"+monthStr2;
					}
					String yearMonth = year+"-"+monthStr2;//2017-04
					
					//当年当月新增顾客应减斤数
					Record yjjsRecord1 = Db.findFirst(yjjsSql,yearMonth,organIds);
					double yjjs1 = 0d;
					if(yjjsRecord1.getDouble("yjjs")!=null ){
						yjjs1 =yjjsRecord1.getDouble("yjjs");
						yjjsSum = yjjsSum +yjjs1;
					}
					if(yjjsSum!=0){
					//当年当月新增顾客实减斤数
					//List<Record> xgkRecord1 = Db.find(xgkSql,yearMonth,organIds);
					double sjjs1 = 0d;
					Record xgkRecord1 = Db.findFirst(sgksjjsSql,organIds,yearMonth);
					
					if(xgkRecord1.getDouble("gjWeight")!=null ){
						sjjsSum = xgkRecord1.getDouble("gjWeight");
					}
					
					
					/*if(xgkRecord1!=null && xgkRecord1.size()>0){
						for (Record xgk:xgkRecord1) {
							String customerIds = xgk.getStr("customerIds");
							Record gjRecord = Db.findFirst(sgksjjsSql,customerIds,yearMonth);
							if(gjRecord!=null ){
								if(gjRecord.getFloat("gjWeight")!=null ){
									sjjs1 = sjjs1+gjRecord.getFloat("gjWeight");
								}
								
							}
						}
						sjjsSum = sjjsSum + sjjs1;
					}*/
					if(monthStr.equals(yearMonth)){
						r.set("ljjzl", (sjjsSum/yjjsSum)*100);
						break;
					}
				}
			}
			
				r.set("orgname", orgList.get(i).getStr("orgname"));
				r.set("organids", orgList.get(i).getStr("organids"));
				records.add(r);
			}
		}
		renderJson(records);
		
	
	}

}
